@echo off
chcp 65001 >nul
setlocal enabledelayedexpansion

:: ========== 数据库配置 ==========
set SERVER=localhost\SQLEXPRESS
set DATABASE=StickInspectDB
set USERNAME=sa
set PASSWORD=123
set TABLE_NAME=PolarDisTable
:: ==============================

set TIMESTAMP=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%
set OUTPUT_XLSX=用户数据_%TIMESTAMP%.xlsx
set OUTPUT_CSV=用户数据_%TIMESTAMP%.csv
set TEMP_CSV=temp_data.csv

echo ========================================
echo       数据库导出工具
echo ========================================
echo.

echo [1/5] 检查数据库连接...
sqlcmd -S "%SERVER%" -U "%USERNAME%" -P "%PASSWORD%" -Q "SELECT '连接成功' as Status" -h -1 >nul 2>&1
if errorlevel 1 (
    echo [错误] 数据库连接失败！
    echo 请检查数据库服务是否启动
    pause
    exit /b 1
)
echo [成功] 数据库连接正常

echo.
echo [2/5] 检查表是否存在...
sqlcmd -S "%SERVER%" -U "%USERNAME%" -P "%PASSWORD%" -d "%DATABASE%" -Q "SELECT COUNT(*) as 记录数 FROM %TABLE_NAME%" -h -1
if errorlevel 1 (
    echo [错误] 表 %TABLE_NAME% 不存在！
    pause
    exit /b 1
)

echo.
echo [3/5] 导出数据到临时文件...
:: 先清空临时文件
if exist "%TEMP_CSV%" del "%TEMP_CSV%"

:: 导出数据（保留表头，使用-W去除尾随空格）
sqlcmd -S "%SERVER%" -d "%DATABASE%" -U "%USERNAME%" -P "%PASSWORD%" -Q "SELECT * FROM %TABLE_NAME%" -s "," -W > "%TEMP_CSV%"

if errorlevel 1 (
    echo [错误] 数据库查询失败
    if exist "%TEMP_CSV%" del "%TEMP_CSV%"
    pause
    exit /b 1
)

:: 检查数据是否导出成功
for %%F in ("%TEMP_CSV%") do set filesize=%%~zF
if !filesize! LEQ 1 (
    echo [错误] 导出的数据文件为空
    if exist "%TEMP_CSV%" del "%TEMP_CSV%"
    pause
    exit /b 1
)

echo [成功] 数据导出完成，共导出: 
find /c /v "" < "%TEMP_CSV%"

echo.
echo [4/5] 创建Excel文件...
powershell -Command "
try {
    if (Test-Path '%TEMP_CSV%') {
        # 读取CSV文件并过滤分隔线和空行
        `$content = Get-Content '%TEMP_CSV%' | Where-Object { `$_ -notmatch '^[-]+$' -and `$_ -notmatch '^`$' }
        
        if (`$content.Count -eq 0) {
            throw 'CSV文件内容为空'
        }
        
        # 创建Excel对象
        `$excel = New-Object -ComObject Excel.Application
        `$excel.Visible = `$false
        `$excel.DisplayAlerts = `$false
        `$workbook = `$excel.Workbooks.Add()
        `$worksheet = `$workbook.Worksheets.Item(1)
        `$worksheet.Name = '极坐标数据'
        
        # 写入数据到Excel
        `$row = 1
        foreach (`$line in `$content) {
            # 跳过空行和分隔线
            if (`$line.Trim() -eq '' -or `$line -match '^[-]+$') {
                continue
            }
            
            `$cells = `$line -split ','
            `$col = 1
            foreach (`$cell in `$cells) {
                `$worksheet.Cells.Item(`$row, `$col) = `$cell.Trim()
                `$col++
            }
            `$row++
        }
        
        # 获取实际的数据列数（从第一行获取）
        if (`$content.Count -gt 0) {
            `$firstLine = `$content[0] -replace '^[-]+$', ''
            if (`$firstLine.Trim() -ne '') {
                `$colCount = (`$firstLine -split ',').Count
                
                # 根据列数动态生成表头范围
                if (`$colCount -gt 0) {
                    `$headerRange = `$worksheet.Range(`"A1:`$([char](64 + `$colCount))1`")
                    `$headerRange.Font.Bold = `$true
                    `$headerRange.Interior.Color = 15773696  # 浅蓝色背景
                }
            }
        }
        
        # 自动调整列宽
        `$worksheet.UsedRange.EntireColumn.AutoFit()
        
        # 添加边框
        `$dataRange = `$worksheet.UsedRange
        `$dataRange.Borders.LineStyle = 1
        
        # 保存文件
        `$workbook.SaveAs('%OUTPUT_XLSX%')
        `$workbook.Close()
        `$excel.Quit()
        
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject(`$excel) | Out-Null
        
        Write-Host '[成功] Excel文件创建完成！'
        
        # 同时保存一份CSV备份（同样过滤掉不需要的行）
        `$content | Where-Object { `$_ -notmatch '^[-]+$' } | Out-File '%OUTPUT_CSV%' -Encoding UTF8
        Write-Host '[成功] CSV备份文件也已创建'
        
    } else {
        Write-Host '[错误] 临时文件未创建'
    }
} catch {
    Write-Host '[错误] ' `$_.Exception.Message -ForegroundColor Red
}
"

echo.
echo [5/5] 清理和完成...
:: 清理临时文件
if exist "%TEMP_CSV%" (
    del "%TEMP_CSV%"
    echo [清理] 临时文件已删除
)

echo.
echo ========================================
if exist "%OUTPUT_XLSX%" (
    echo 导出成功！
    echo.
    echo 生成的文件：
    echo   - Excel文件: %OUTPUT_XLSX%
    if exist "%OUTPUT_CSV%" echo   - CSV备份: %OUTPUT_CSV%
    echo.
    set /p CHOICE="是否打开Excel文件？(Y/N): "
    if /i "!CHOICE!"=="Y" (
        echo 正在打开文件...
        start "" "%OUTPUT_XLSX%"
    )
) else (
    echo 导出失败！
)

echo.
echo 程序执行完毕！
timeout /t 2 >nul